IF OBJECT_ID('dbo.vwAgreementCurrentBudgets') IS NOT NULL
BEGIN
    DROP VIEW dbo.vwAgreementCurrentBudgets
    IF OBJECT_ID('dbo.vwAgreementCurrentBudgets') IS NOT NULL
        PRINT '<<< FAILED DROPPING VIEW dbo.vwAgreementCurrentBudgets >>>'
    ELSE
        PRINT '<<< DROPPED VIEW dbo.vwAgreementCurrentBudgets >>>'
END
GO
-- =======================================================================
-- Object Name: (VIEW) dbo.vwAgreementCurrentBudgets
--
-- Author:      Kevin Cashman, Parallax Consulting LLC
--
-- Create date: 8/23/2007
--
-- Description: View to get Current Budget data, Based on GETDATE()
--        If Agreement is Pending (BudgetYear=0), the return
--        Year=1, Otherwise, current overlapping year with today.
--
-- Used BY Procedure/Report: (MOST)
--
--------------------------------------------------------------------------
-- Date        Initials  Modification
--------------------------------------------------------------------------
-- 8/23/07     KC        Created
-- 9/7/07      KC        Changed to use table.
-- 9/8/07      KC        Added IndirectCostBasis and Rate
-- 9/10/07     KC        Added Sequence Year.
-- 12/28/07    KC        Added AND IsAdjustment = 0 check
--
-- =======================================================================
CREATE VIEW dbo.vwAgreementCurrentBudgets
AS
SELECT    AB.AgreementId AS AgreementId,
          AB.FundNumber AS FundNumber,
          AB.BudgetYear AS BudgetYear,
          AB.SequenceYear AS SequenceYear,
          AB.BudgetStartDate AS BudgetStartDate,
          AB.BudgetEndDate AS BudgetEndDate,
          AB.BudgetViewType AS BudgetViewType,
          AB.BudgetDirectCost AS BudgetDirectCost,
          AB.BudgetIndirectCost AS BudgetIndirectCost,
          AB.IndirectCostBasis AS IndirectCostBasis,
          AB.IndirectCostRate AS IndirectCostRate
FROM      dbo.AgrAgreement_Budget AB
     INNER JOIN 
     (
        SELECT    A.Id AS AgreementId,
                  MAX(BUDG.MAXYear) AS BudgetYear
        FROM   dbo.AgrAgreement A
             INNER JOIN (
                     SELECT    AgreementId,
                               MAX(BudgetYear) AS MAXYear
                     FROM      dbo.AgrAgreement_Budget
                     WHERE     (GETDATE() BETWEEN BudgetStartDate AND BudgetEndDate) 
                               AND IsAdjustment = 0
                     GROUP BY AgreementId          
                     UNION
                     SELECT    AgreementId,
                               MAX(BudgetYear) AS MAXYear
                     FROM      dbo.AgrAgreement_Budget
                     WHERE     BudgetEndDate < GETDATE() AND IsAdjustment = 0
                     GROUP BY AgreementId 
                     UNION
                     SELECT    AgreementId,
                               MIN(BudgetYear) AS MAXYear
                     FROM      dbo.AgrAgreement_Budget
                     WHERE     BudgetEndDate < GETDATE() AND IsAdjustment = 0
                     GROUP BY AgreementId) BUDG ON A.Id = BUDG.AgreementId 
                     GROUP BY A.Id) BUDGVALS ON AB.AgreementId = BUDGVALS.AgreementId AND AB.BudgetYear = BUDGVALS.BudgetYear 

GO

IF OBJECT_ID('dbo.vwAgreementCurrentBudgets') IS NOT NULL
    PRINT '<<< CREATED VIEW dbo.vwAgreementCurrentBudgets >>>'
ELSE
    PRINT '<<< FAILED CREATING VIEW dbo.vwAgreementCurrentBudgets >>>'
GO
